﻿using Domain.Entity;
using DtoFw.Query;
using Repository.Base;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Util;

namespace Repository
{
    public class CaseRepository : BaseRepository<CaseEntity>
    {
        private readonly SnowFlakeGenerator worker;
        public CaseRepository(SnowFlakeGenerator snowFlakeGenerator) : base(snowFlakeGenerator)
        {
            this.worker = snowFlakeGenerator;
        }



        /// <summary>
        /// 根据物品编号查询案件信息
        /// </summary>
        /// <param name="evidenceNo"></param>
        /// <returns></returns>
        public SourceEviDto GetCaseEntityByEviNo(string evidenceNo, long OrganId)
        {
            string sql = @$"select  A2.CaseNo,A2.CaseName,HostUserOrganName,HostUserName,InspectionNo,a1.OrganId,A1.No EvidenceNo,a1.name EvidenceName,a2.CaseTime,a1.CreateTime,a1.Creator
                              from case_physical_evidence A1
                                LEFT JOIN cases A2 on A1.CaseId = A2.ID
                                WHERE A1.IsDeleted = '0'  AND A2.IsDeleted = '0' AND A1.`No`= '{evidenceNo}' and a1.OrganId={OrganId}";

            return base.GetDb().Ado.SqlQuerySingle<SourceEviDto>(sql);


        }

        /// <summary>
        /// 流转记录
        /// </summary>
        /// <param name="evidenceNo"></param>
        /// <param name="OrganId"></param>
        /// <returns></returns>
        public List<CasePhysicalEvidenceTransfereEntity> GetTansByEviNo(string evidenceNo, long OrganId)
        {
            string sql = @$"select  a1.*  from case_physical_evidence_transfere a1
                                left join case_physical_evidence a2 on a1.EvidenceId = a2.id
                            where a2.`No`= '{evidenceNo}'  and a2.OrganId = {OrganId} and a2.IsDeleted = '0' order by a1.CreateTime asc";

            return base.GetDb().Ado.SqlQuery<CasePhysicalEvidenceTransfereEntity>(sql);

        }

        #region 第三方案件信息查询

        public List<dynamic> GetThirdSystemCase(string JJBH, string ASJBH, string XCKYBH)
        {
            string wh = " where 1=1 ";
            if (!string.IsNullOrEmpty(ASJBH))
            {
                wh += $" AND ASJBH='{ASJBH}' ";
            }
            if (!string.IsNullOrEmpty(JJBH))
            {
                wh += $" AND JJDBH='{JJBH}' ";
            }
            if (!string.IsNullOrEmpty(XCKYBH))
            {
                wh += $" AND XCKYBH='{XCKYBH}' ";
            }

            string sqlJq = $"select JQBH from tb_st_jq {wh} ";
            var jqResult = DbContext.ThirdMysql.Ado.SqlQuery<string>(sqlJq);

            List<dynamic> list = new List<dynamic>();
            if (jqResult != null && jqResult.Count > 0)
            {
                var ids = string.Join("','", jqResult);
                
                string sql = @$"select  AJMC,ASJBH,JJBH,AJLBDM,FXASJSJ,ZBR_XM,BADW_GAJGJGDM,BADW_GAJGMC,ASJFSDD_DZMC ASJFSDD_DZID,JYAQ 
                    from tb_st_asj where  JJBH in('{ids}') ";
                list = DbContext.ThirdMysql.Ado.SqlQuery<dynamic>(sql);
                if (list == null || list.Count == 0)
                {
                    sql = @$"select  AJMC,ASJBH,JJBH,AJLBDM,FXASJSJ,ZBR_XM,BADW_GAJGJGDM,BADW_GAJGMC,ASJFSDD_DZMC ASJFSDD_DZID,JYAQ
                              from tb_st_xzasj where  JJBH in('{ids}') ";
                    list = DbContext.ThirdMysql.Ado.SqlQuery<dynamic>(sql);
                }
            }
            return list;
        }

        #endregion 第三方案件信息查询
    }
}
